import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description 一个成绩表，要求60分以上的红色，60分一下的绿色，然后还有有平均分，字体格式为居中
 * @Author hhy
 * @Date 2020/10/20 15:00
 */
public class GradesExcel {
    public static void main(String[] args) throws IOException {
        List<Student> list = new ArrayList<>(){{
            add(new Student(1,"liliya",99.0));
            add(new Student(2,"luosaliya",55.0));
            add(new Student(3,"buluoniya",60.5));
            add(new Student(4,"qwq",30.0));
            add(new Student(5,"QAQ",50.0));
        }};


        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("成绩表");
        File file = new File("grader.xls");


        CellStyle style1 = workbook.createCellStyle();
        //这个样式是绿色的背景
        CellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setAlignment(HorizontalAlignment.CENTER);
        CellStyle style3 = workbook.createCellStyle();
        style3.setFillForegroundColor(IndexedColors.RED1.getIndex());
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style3.setAlignment(HorizontalAlignment.CENTER);

        //设置水平居中
        style1.setAlignment(HorizontalAlignment.CENTER);
        //将第一二三列的数据都变成居中的
        sheet.setDefaultColumnStyle(0,style1);
        sheet.setDefaultColumnStyle(1,style1);
        sheet.setDefaultColumnStyle(2,style1);

        try ( FileOutputStream outputStream = new FileOutputStream(file)) {
            //设置第一行
            Row row0 = sheet.createRow(0);
            Cell cell_00 = row0.createCell(0);
            Cell cell_01 = row0.createCell(1);
            Cell cell_02 = row0.createCell(2);
            cell_00.setCellValue("序号");
            cell_01.setCellValue("名字");
            cell_02.setCellValue("成绩");

            //酱对象的是数据写入，然后根据不同条件来添加不同的样式
            for (int i = 0; i < list.size(); i++) {
                Student student = list.get(i);
                Row row = sheet.createRow(i + 1);
                //创建各个单元格
                Cell cell0 = row.createCell(0);
                Cell cell1 = row.createCell(1);
                Cell cell2 = row.createCell(2);

                Integer id = student.getId();
                String name = student.getName();
                Double greade = student.getGreade();

                //成绩小于60分的来一个样式
                if (greade<60){
                    //cell_02.setCellStyle(style2);
                    System.out.println("有不及格的"+name);
                    cell2.setCellStyle(style2);
                }else {
                    cell2.setCellStyle(style3);
                }

                cell0.setCellValue(id);
                cell1.setCellValue(name);
                cell2.setCellValue(greade);
            }

            int avg = sheet.getLastRowNum()+1;

            //根据list的长度来拿到最后一行。
            Row rowAverage = sheet.createRow(avg);
            rowAverage.createCell(0).setCellValue("平均数");
            rowAverage.createCell(2).setCellFormula("AVERAGE(C2:C"+(list.size()+1)+")");


            Row rowSum = sheet.createRow(rowAverage.getRowNum() + 1);
            rowSum.createCell(0).setCellValue("成绩总和");
            rowSum.createCell(2).setCellFormula("SUM(C2:C"+(list.size()+1)+")");

            Row rowMax = sheet.createRow(rowSum.getRowNum() + 1);
            rowMax.createCell(0).setCellValue("最高分");
            rowMax.createCell(2).setCellFormula("MAX(C2:C"+(list.size()+1)+")");


            workbook.write(outputStream);
        }
    }
}
